﻿using Domain.Entity;
using DtoFw;
using DtoFw.Query;
using Repository;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Util;
using DtoFw.Common.Query;

namespace Service
{
    public class RoomService
    {
        private readonly RoomAreaRepository _roomAreaRepository;
        private readonly RoomBoxRepository _roomBoxRepository;
        private readonly RoomCabinetRepository _roomCabinetRepository;
        public RoomService(RoomAreaRepository roomAreaRepository,
            RoomBoxRepository roomBoxRepository, RoomCabinetRepository roomCabinetRepository)
        {
            this._roomAreaRepository = roomAreaRepository;
            this._roomBoxRepository = roomBoxRepository;
            this._roomCabinetRepository = roomCabinetRepository;
        }
        /// <summary>
        /// 添加区域
        /// </summary>
        /// <returns></returns>
        public BaseResult PostArea(AreaPostDto dto)
        {
            var entity = AutoMapperExtension.MapTo<AreaPostDto, RoomAreaEntity>(dto);
            entity.Creator = dto.UserCode;
            entity.CreatorId = dto.UserId;
            if (_roomAreaRepository.Insert(entity) > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("区域增加失败");
        }
        /// <summary>
        /// 编辑区域
        /// </summary>
        /// <returns></returns>
        public BaseResult PutArea(AreaPutDto dto)
        {
            var entity = _roomAreaRepository.Get(new BaseQuery() { Id = dto.Id });
            if (entity == null)
                return BaseResult.Failure("参数异常");
            if (entity.OrganId != dto.OrganId)
            {
                return BaseResult.Failure("非本单位所管理，不可以修改！");
            }
            entity.Modifyer = dto.UserCode;
            entity.ModifyerId = dto.UserId;
            entity = AutoMapperExtension.MapTo<AreaPutDto, RoomAreaEntity>(dto, entity);
            if (_roomAreaRepository.Save(entity)?.Id > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("区域增加失败");
        }
        /// <summary>
        /// 查询区域
        /// </summary>
        /// <returns></returns>
        public BaseGenericResult<List<AreaQueryDto>> QueryAreas(CommonQuery query)
        {
            var list = _roomAreaRepository.GetDb().Queryable<RoomAreaEntity>().Where(v => v.OrganId == query.OrganId && !v.IsDeleted)?.OrderBy(v => v.OrderBy)
                 .ToList();
            var dtoList = AutoMapperExtension.MapTo<RoomAreaEntity, AreaQueryDto>(list);
            return BaseGenericResult<List<AreaQueryDto>>.Succeed(dtoList);
        }

        public List<dynamic> QueryEvidenceByCabinet(long cabinetId, string startTime)
        {
            string sql = $@"SELECT s3.`Id` as `ID`,s3.`CaseNo` as `CaseCode`,s3.`CaseName`,s1.`ModifyerTime` as ModifyTime,s1.`Id` as EvidenceID,
                                   (case when s2.DeliveryDicType=2 then 10 
                                         when s2.DeliveryDicType=7 then 7 
                                         when s2.DeliveryDicType=8 then 2 end) as `State`,
                                   s1.`ClassName` as `Type`,'' as EPC,s1.`Name` as EvidenceName,CONCAT(s6.`Code`,s4.`No`) as Cabinet,
                                   CONCAT(s6.`Code`,s4.`No`) as CabinetCode,CONCAT(s6.`Code`,s4.`No`) as Cell,CONCAT(s6.`Code`,s4.`No`,'-',s5.`Name`) as `CellCode` 
                              FROM case_physical_evidence s1 
                              join cases s3 on s1.`CaseId`=s3.`Id`
                              left join(select * from case_physical_evidence_transfere where `DeliveryDicType` in(2,7,8) AND `HistoryStatus`=0) s2 on s2.`EvidenceId`=s1.`Id` 
                              left join room_box s5 on s5.`Id`=s2.LocationId
                              left join room_cabinet s4 on s5.`CabinetId`=s4.`Id` 
                              left join room_area s6 on s6.`Id`=s4.`AreaId` 
                             where s1.`Status`=1 
                               AND s1.`IsDeleted`=0
                               AND s4.`Id`=" + cabinetId;
            if (!string.IsNullOrEmpty(startTime))
            {
                sql += $" AND s2.`CreateTime`>='{startTime}'";
            }
            return this._roomCabinetRepository.GetDb().SqlQueryable<dynamic>(sql).ToList();
        }

        /// <summary>
        /// 查询区域
        /// </summary>
        /// <returns></returns>
        public BaseGenericResult<AreaQueryDto> QueryAreasById(long id)
        {
            var info = _roomAreaRepository.Get(new BaseQuery() { Id = id });

            var dtoInfo = AutoMapperExtension.MapTo<RoomAreaEntity, AreaQueryDto>(info);

            return BaseGenericResult<AreaQueryDto>.Succeed(dtoInfo);
        }

        /// <summary>
        /// 删除区域
        /// </summary>
        /// <returns></returns>
        public BaseResult DeleteArea(DeleteSingleDto query)
        {
            var entity = _roomAreaRepository.Get(new BaseQuery() { Id = query.Id });
            if (_roomCabinetRepository.Count(new BaseQuery(), t => t.Where(m => m.AreaId == query.Id)) > 0)
                return BaseResult.Failure("删除失败，该区域下面还包含柜子");
            if (_roomAreaRepository.Delete(new Domain.Base.BaseOperate() { Id = query.Id }) > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("删除失败");
        }

        private string CabinetVerify(CabinetDto dto)
        {
            if (string.IsNullOrWhiteSpace(dto.Name))
                return "柜子名称不能为空";
            if (dto.AreaId < 1)
                return "所选区域不存在";
            return "";
        }

        /// <summary>
        /// 添加柜子
        /// </summary>
        /// <returns></returns>
        public BaseResult PostCabinet(CabinetPostDto dto)
        {
            string str = CabinetVerify(dto);
            if (!string.IsNullOrWhiteSpace(str))
                return BaseResult.Failure(str);
            var entity = AutoMapperExtension.MapTo<CabinetPostDto, RoomCabinetEntity>(dto);
            entity.Creator = dto.UserCode;
            entity.CreatorId = dto.UserId;
            if (_roomCabinetRepository.Insert(entity) > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("柜子添加失败");
        }

        /// <summary>
        /// 添加密集柜列
        /// </summary>
        /// <returns></returns>
        public BaseResult AddMCabinetsCol(CabinetPostDto dto)
        {
            RoomCabinetEntity entity = _roomCabinetRepository.Get(new BaseQuery() { Id = dto.Id });
            entity.Columns += 1;

            if (_roomCabinetRepository.Save(entity)?.Id > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("密集柜添加列失败");
        }

        /// <summary>
        /// Copy密集柜列
        /// </summary>
        /// <returns></returns>
        public BaseResult CopyMCabinets(long cabinetId, int colNO)
        {
            var list = _roomBoxRepository.Query(new BaseQuery(), t => t.Where(m => m.CabinetId == cabinetId && m.ColNO == colNO)).ToList();

            RoomCabinetEntity entity = _roomCabinetRepository.Get(new BaseQuery() { Id = cabinetId });
            entity.Columns += 1;
            _roomCabinetRepository.Save(entity);

            if (list.Any())
            {
                //list.ToList().ForEach(delegate (RoomBoxEntity entity) { entity.ColNO = entity.Columns; entity.Id = 0; });
                foreach (var item in list)
                {
                    item.ColNO = entity.Columns; item.Id = 0;
                }

                if (_roomBoxRepository.InsertBatch(list) > 0)
                    return BaseResult.Succeed();
            }
            else
            {
                return BaseResult.Succeed();
            }

            return BaseResult.Failure("复制列失败");
        }

        /// <summary>
        /// 编辑柜子
        /// </summary>
        /// <returns></returns>
        public BaseResult PutCabinet(CabinetPutDto dto)
        {
            string str = CabinetVerify(dto);
            if (!string.IsNullOrWhiteSpace(str))
                return BaseResult.Failure(str);
            var entity = _roomCabinetRepository.Get(new BaseQuery() { Id = dto.Id });

            bool ischangeTotal = dto.Total != entity.Total;
            if (entity == null)
                return BaseResult.Failure("参数异常");
            entity.Modifyer = dto.UserCode;
            entity.ModifyerId = dto.UserId;
            entity = AutoMapperExtension.MapTo<CabinetPutDto, RoomCabinetEntity>(dto, entity);
            if (_roomCabinetRepository.Save(entity)?.Id > 0)
            {

                if (ischangeTotal)
                {
                    var dbContext = _roomBoxRepository.GetDb();

                    var list = dbContext.Queryable<RoomBoxEntity>()
                        .Where(x => x.CabinetId == dto.Id).ToList();

                    list.ToList().ForEach(v => v.Total = dto.Total);
                    var result = dbContext.Updateable(list).UpdateColumns(it => new { it.Total }).ExecuteCommand();
                }

                return BaseResult.Succeed();
            }
            return BaseResult.Failure("柜子增加失败");
        }

        /// <summary>
        /// 复制柜子 复制密集柜的列暂缓
        /// </summary>
        /// <returns></returns>
        public BaseResult CopyCabinet(long id)
        {
            var result = false;
            //查询当前柜子信息
            var linqCabinet = _roomCabinetRepository.Query(new BaseQuery() { Id = id });
            //新增柜子
            long newid = _roomCabinetRepository.Insert(linqCabinet.Single());

            if (newid > 0)
            {
                result = true;
                //查询柜子的箱子信息
                var linqBox = _roomBoxRepository.Query(new BaseQuery() { }).Where(v => v.CabinetId == id).ToList();

                if (linqBox.Count > 0)
                {
                    linqBox.ForEach(delegate (RoomBoxEntity entity) { entity.CabinetId = newid; entity.Id = 0; });
                    //新增箱子
                    result &= _roomBoxRepository.InsertBatch(linqBox) > 0;
                }
            }

            if (result)
                return BaseResult.Succeed();

            return BaseResult.Failure("复制柜子失败");
        }


        /// <summary>
        /// 查询柜子
        /// </summary>
        /// <returns></returns>
        public BaseGenericResult<List<CabinetQueryDto>> QueryCabinets(long areaId,long organId)
        {
            var linq = _roomCabinetRepository.Query(new BaseQuery() { });
            if (areaId > 0)
                linq = linq.Where(x => x.AreaId == areaId);
            linq.Where(v=>v.OrganId == organId);
            var list = linq?.OrderBy(v => v.OrderBy).ToList();
            var dtoList = AutoMapperExtension.MapTo<RoomCabinetEntity, CabinetQueryDto>(list);
            return BaseGenericResult<List<CabinetQueryDto>>.Succeed(dtoList);
        }


        /// <summary>
        /// 删除柜子
        /// </summary>
        /// <returns></returns>
        public BaseResult DeleteCabinet(DeleteSingleDto query)
        {
            if (_roomBoxRepository.Count(new BaseQuery(), t => t.Where(m => m.CabinetId == query.Id)) > 0)
                return BaseResult.Failure("删除失败，该柜子下面还包含箱子");

            if (_roomCabinetRepository.Delete(new Domain.Base.BaseOperate() { Id = query.Id }) > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("删除失败");
        }

        /// <summary>
        /// 删除密集柜列
        /// </summary>
        /// <returns></returns>
        public BaseResult DeleteMiCabinet(long cabinetId, int colNO)
        {
            if (_roomBoxRepository.Count(new BaseQuery(), t => t.Where(m => m.CabinetId == cabinetId && m.ColNO == colNO)) > 0)
                return BaseResult.Failure("删除失败，该柜子下面还包含箱子");


            if (_roomBoxRepository.Delete(new Domain.Base.BaseOperate(), t => t.Where(m => m.ColNO == colNO && m.CabinetId == cabinetId)) > 0)
            {

            }

            RoomCabinetEntity entity = _roomCabinetRepository.Get(new BaseQuery() { Id = cabinetId });
            entity.Columns -= 1;
            if (_roomCabinetRepository.Save(entity)?.Id > 0)
                return BaseResult.Succeed();

            return BaseResult.Failure("删除失败");
        }




        private string BoxVerify(BoxDto dto)
        {
            if (string.IsNullOrWhiteSpace(dto.Name))
                return "柜子名称不能为空";
            if (dto.AreaId < 1)
                return "所选区域不存在";
            if (dto.CabinetId < 1)
                return "所选柜子不存在";
            return "";
        }

        /// <summary>
        /// 添加箱子
        /// </summary>
        /// <returns></returns>
        public BaseResult PostBox(List<List<BoxPostDto>> array)
        {
            if(array.Count == 0)
                return BaseResult.Succeed();

            var _CabinetId = array[0][0].CabinetId;
            bool result = true;
            foreach (var item in array)
            {
                if (item.Count == 0) 
                    continue;
                var _first = item.FirstOrDefault();
                var creator = _first.UserCode;
                var creatorId = _first.UserId;

                //新增
                var dto = item.Where(v => v.IsAdd == 1).ToList();
                var list = AutoMapperExtension.MapTo<BoxPostDto, RoomBoxEntity>(dto);
                if (dto.Any())
                {
                    //读取容量
                    var cabinetEntity = _roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == _first.CabinetId)).First();
                    list.ForEach(delegate (RoomBoxEntity entity) { entity.Total = cabinetEntity.Total; entity.Creator = creator; entity.CreatorId = creatorId; });
                    result = _roomBoxRepository.InsertBatch(list) > 0;
                }
                //修改
                dto = item.Where(v => v.IsAdd == 2).ToList();
                if (dto.Any())
                {
                    list = AutoMapperExtension.MapTo<BoxPostDto, RoomBoxEntity>(dto);
                    list.ForEach(delegate (RoomBoxEntity entity) { entity.Modifyer = creator; entity.ModifyerId = creatorId; entity.ModifyerTime = DateTime.Now; });
                    var dbContext = _roomBoxRepository.GetDb();
                    dbContext.Updateable(list).UpdateColumns(it => new { it.Name, it.Port, it.Total,it.ColNO, it.Modifyer, it.ModifyerId, it.ModifyerTime }).ExecuteCommand();
                }
                //删除
                dto = item.Where(v => v.IsAdd == 3).ToList();
                if (dto.Any())
                {
                    _roomBoxRepository.Delete(new Domain.Base.BaseOperate() { Ids = dto.Select(v=>v.Id).ToList() });
                }
            }
            //var _Cabinet = this._roomCabinetRepository.Query(v=>v.Id == _CabinetId);
            //if(_Cabinet != null && _Cabinet.) {
            if (result)
                return BaseResult.Succeed();

            return BaseResult.Failure("箱子添加失败");

        }
        /// <summary>
        /// 编辑箱子
        /// </summary>
        /// <returns></returns>
        public BaseResult PutBox(BoxPutDto dto)
        {
            string str = BoxVerify(dto);
            if (string.IsNullOrWhiteSpace(str))
                return BaseResult.Failure(str);
            var entity = _roomBoxRepository.Get(new BaseQuery() { Id = dto.Id });
            if (entity == null)
                return BaseResult.Failure("参数异常");
            entity.Modifyer = dto.UserCode;
            entity.ModifyerId = dto.UserId;
            entity = AutoMapperExtension.MapTo<BoxPutDto, RoomBoxEntity>(dto, entity);
            if (_roomBoxRepository.Save(entity)?.Id > 0)
                return BaseResult.Succeed();
            return BaseResult.Failure("箱子增加失败");
        }
        /// <summary>
        /// 查询箱子
        /// </summary>
        /// <returns></returns>
        public BaseGenericResult<BoxQueryAllDto> QueryBoxs(long cabinetId, int col, int direction,long organid)
        {
            var list = _roomBoxRepository.GetDb()
                .Queryable<RoomBoxEntity>()
                .Where(x => !x.IsDeleted)
                .WhereIF(cabinetId > 0, x => x.CabinetId == cabinetId)
                .WhereIF(col > 0, x => x.ColNO == col)
                .WhereIF(direction >= 0, x => x.Direction == direction)?.ToList();

            BoxQueryAllDto dto = new BoxQueryAllDto();
            if (list.Any())
            {
                var dtoList = AutoMapperExtension.MapTo<RoomBoxEntity, BoxQueryDto>(list);

                dto.col = dtoList.Max(v => v.Columns.Value);
                dto.row = dtoList.Max(v => v.Rows.Value);
                for (int i = 1; i <= dto.row; i++)
                    dto.rows.Add(dtoList.Where(v => v.Rows == i).ToList());
            }

            return BaseGenericResult<BoxQueryAllDto>.Succeed(dto);
        }

        /// <summary>
        /// 删除箱子
        /// </summary>
        /// <returns></returns>
        public BaseResult DeleteBoxs(long cabinetId, int col, int direction, int row)
        {
            bool result = true;

            if (direction >= 0)
            {
                if (row > 0)
                {
                    result = _roomBoxRepository.Delete(new Domain.Base.BaseOperate(), v => v.Where(x => x.Direction == direction && x.ColNO == col && x.CabinetId == cabinetId && x.Rows == row)) > 0;
                }
                else
                {
                    //密集柜清空
                    result = _roomBoxRepository.Delete(new Domain.Base.BaseOperate(), v => v.Where(x => x.Direction == direction && x.ColNO == col && x.CabinetId == cabinetId)) > 0;
                }
            }
            else
            {
                if (row > 0)
                {
                    result = _roomBoxRepository.Delete(new Domain.Base.BaseOperate(), v => v.Where(x => x.CabinetId == cabinetId && x.Rows == row)) > 0;
                }
                else
                {
                    //其他柜子清空
                    result = _roomBoxRepository.Delete(new Domain.Base.BaseOperate(), v => v.Where(x => x.CabinetId == cabinetId)) > 0;
                }
            }

            if (result)
                return BaseResult.Succeed();

            return BaseResult.Failure("删除失败");
        }

        /// <summary>
        /// 开柜子
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public BaseGenericResult<List<OpenBoxDto>> OpenBox(OpenBoxDto openBoxDto)
        {
            List<OpenBoxDto> dtoList = new List<OpenBoxDto>();
            try
            {
                var trans = _roomBoxRepository.GetDb().Queryable<CasePhysicalEvidenceTransfereEntity>().Where(v => v.DeliveryNo == openBoxDto.tranNo).ToList();
                List<long> list = trans.Where(v=>v.LocationId.HasValue).Select(v => v.LocationId.Value).Distinct().ToList();
                if(trans.Any() && trans[0].DeliveryDicType == 7)//更换位置
                {
                    //如果当前物语流转操作为更换位置，需要把更换之前的位置也查出来
                    foreach (var evi in trans)
                    {
                        var _first = _roomBoxRepository.GetDb().Queryable<CasePhysicalEvidenceTransfereEntity>().
                            Where(v => v.EvidenceId == evi.EvidenceId && v.Id< evi.Id).
                            OrderBy(v=>v.Id, SqlSugar.OrderByType.Desc).
                            First();
                        if(_first.LocationId.HasValue && !list.Contains(_first.LocationId.Value))
                        {
                            list.Add(_first.LocationId.Value);
                        }
                    }
                }
                foreach (var item in list)
                {
                    OpenBoxDto dto = new OpenBoxDto();
                    RoomBoxEntity boxentity = _roomBoxRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == item)).Single();
                    if (boxentity == null)
                        continue;

                    RoomCabinetEntity cabinetEntity = _roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == boxentity.CabinetId)).Single();
                    if (cabinetEntity == null || string.IsNullOrWhiteSpace(cabinetEntity.Addr))
                        continue;

                    dto.ComNo = cabinetEntity.Addr;
                    dto.Port = boxentity.Port ?? 0;
                    dto.Key = cabinetEntity.CabinetCode;
                    //密集柜
                    if (cabinetEntity.CabinetCode == "1" && cabinetEntity.Smart.Value)
                    {
                        RoomAreaEntity areaEntity = _roomAreaRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == cabinetEntity.AreaId)).Single();
                        dto.CabinetType = 1;
                        dto.AreaNo = areaEntity.WarehouseNo;
                        dto.ColumnNo = boxentity.ColNO.Value;
                        if (boxentity.Direction == 0 && cabinetEntity.FixedColumn == "1")
                        {
                            dto.ColumnNo = boxentity.ColNO.Value + 1;
                        }
                        else if (boxentity.Direction == 1 && cabinetEntity.FixedColumn == "2")
                        {
                            dto.ColumnNo = boxentity.ColNO.Value + 1;
                        }
                        dto.FixedColumn = cabinetEntity.FixedColumn;
                        if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.ColumnNo == dto.ColumnNo && v.FixedColumn == dto.FixedColumn).Any())
                            dtoList.Add(dto);
                    }
                    else if (cabinetEntity.CabinetCode == "2" && cabinetEntity.Smart.Value) //智能柜
                    {
                        //58 01 02 03 ff 00 80 8B
                        dto.CabinetType = 2;
                        dto.Module = cabinetEntity.ModuleNo;
                        if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.Module == dto.Module && v.Port == dto.Port).Any())
                            dtoList.Add(dto);
                    }
                    else if (cabinetEntity.CabinetCode == "3") //货架
                    {
                        //58 01 02 03 ff 00 80 8B
                        dto.CabinetType = 3;
                        dto.Module = cabinetEntity.ModuleNo;
                        dto.ColumnNo = boxentity.ColNO ?? 0;//行号
                        dto.ComNo = cabinetEntity.Addr;
                        if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.Module == dto.Module && v.Port == dto.Port).Any())
                            dtoList.Add(dto);
                    }
                    else if (cabinetEntity.CabinetCode == "GPG") //光盘柜
                    {
                        dto.CabinetType = 4;//光盘柜
                        dto.Port = boxentity.Rows ?? 0 + 1;//行号
                        dto.ColumnNo = boxentity.Columns ?? 0 + 1;//行号
                        dto.Module = cabinetEntity.ModuleNo;
                        dto.ComNo = cabinetEntity.Addr;
                        if (!dtoList.Where(v => v.AreaNo == dto.AreaNo && v.Port == dto.Port && v.Module == dto.Module && v.ColumnNo == dto.ColumnNo).Any())
                            dtoList.Add(dto);
                    }
                }
                if (dtoList.Count > 0)
                    return BaseGenericResult<List<OpenBoxDto>>.Succeed(dtoList);
                else
                    return BaseGenericResult<List<OpenBoxDto>>.Failure(dtoList, "您选择的不是智能柜，不需要开柜");
            }
            catch (Exception)
            {
                return BaseGenericResult<List<OpenBoxDto>>.Failure(dtoList, "您选择的不是智能柜，不需要开柜");
            }
        }

        public RoomCabinetEntity QueryBoxCabinetNo(string areaCode,string cabinetCode)
        {
            var areaEntity = this._roomAreaRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Code == areaCode && !x.IsDeleted)).Single();
            if(areaEntity == null)
            {
                return null;
            }
            var cabinetEntity = this._roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.No == cabinetCode && x.AreaId == areaEntity.Id && !x.IsDeleted)).Single();
            if (cabinetEntity == null)
            {
                return null;
            }
            return cabinetEntity;
        }

        /// <summary>
        /// 更改位置开柜子 传拿出东西的柜子List<int> 待开柜子的tranNo
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public BaseGenericResult<List<OpenBoxDto>> ChangeLocOpenBox(OpenBoxDto openBoxDto)
        {
            List<OpenBoxDto> dtoList = new List<OpenBoxDto>();
            try
            {
                foreach (var item in openBoxDto.boxid)
                {
                    OpenBoxDto dto = new OpenBoxDto();
                    RoomBoxEntity boxentity = _roomBoxRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == item)).Single();
                    if (boxentity == null)
                        continue;

                    RoomCabinetEntity cabinetEntity = _roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == boxentity.CabinetId)).Single();
                    if (cabinetEntity == null || string.IsNullOrWhiteSpace(cabinetEntity.Addr))
                        continue;

                    GetOpenBox(dtoList, dto, boxentity, cabinetEntity);
                }

                var trans = _roomBoxRepository.GetDb().Queryable<CasePhysicalEvidenceTransfereEntity>()
                      .Where(v => v.DeliveryNo == openBoxDto.tranNo).ToList();

                List<long?> list = trans.Select(v => v.LocationId).Distinct().ToList();

                foreach (var item in list)
                {
                    if (item.HasValue)
                    {
                        long id = item.Value;
                        OpenBoxDto dto = new OpenBoxDto();
                        RoomBoxEntity boxentity = _roomBoxRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == id)).Single();
                        if (boxentity == null)
                            continue;

                        RoomCabinetEntity cabinetEntity = _roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == boxentity.CabinetId)).Single();
                        if (cabinetEntity == null || string.IsNullOrWhiteSpace(cabinetEntity.Addr))
                            continue;

                        GetOpenBox(dtoList, dto, boxentity, cabinetEntity);
                    }
                }


                if (dtoList.Count > 0)
                    return BaseGenericResult<List<OpenBoxDto>>.Succeed(dtoList);
                else
                    return BaseGenericResult<List<OpenBoxDto>>.Failure(dtoList, "您选择的不是智能柜，不需要开柜");

            }
            catch (Exception)
            {
                return BaseGenericResult<List<OpenBoxDto>>.Failure(dtoList, "您选择的不是智能柜，不需要开柜");
            }
        }

        private void GetOpenBox(List<OpenBoxDto> dtoList, OpenBoxDto dto, RoomBoxEntity boxentity, RoomCabinetEntity cabinetEntity)
        {
            dto.ComNo = cabinetEntity.Addr;

            //密集柜
            if (cabinetEntity.CabinetCode == "1" && cabinetEntity.Smart.Value)
            {
                RoomAreaEntity areaEntity = _roomAreaRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == cabinetEntity.AreaId)).Single();
                dto.CabinetType = 1;
                dto.AreaNo = areaEntity.WarehouseNo;
                dto.ColumnNo = boxentity.ColNO.Value;
                if (boxentity.Direction == 0 && cabinetEntity.FixedColumn == "1")
                {
                    dto.ColumnNo = boxentity.ColNO.Value + 1;
                }
                else if (boxentity.Direction == 1 && cabinetEntity.FixedColumn == "2")
                {
                    dto.ColumnNo = boxentity.ColNO.Value + 1;
                }
                dto.FixedColumn = cabinetEntity.FixedColumn;
                if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.ColumnNo == dto.ColumnNo && v.FixedColumn == dto.FixedColumn).Any())
                    dtoList.Add(dto);

            }
            else if (cabinetEntity.CabinetCode == "2" && cabinetEntity.Smart.Value) //智能柜
            {
                //58 01 02 03 ff 00 80 8B
                dto.CabinetType = 2;
                dto.Module = cabinetEntity.ModuleNo;
                dto.Port = boxentity.Port.Value;
                if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.Module == dto.Module && v.Port == dto.Port).Any())
                    dtoList.Add(dto);

            }
        }

        #region 3D接口相关数据
        /// <summary>
        /// 获取仓库物品数量接口
        /// </summary>
        public List<ThreedimensionalCountEntity> QueryCountByAreaCode(long organId,string areaCode)
        {
            string sql = $@"SELECT IFNULL(sum(if(s4.`Id`>0, 1, 0)),0) as inCount,
                                   IFNULL(sum(if(s5.`Id`>0, 1, 0)),0) as outCount
                              FROM room_area s1
                              join room_cabinet s2 on s1.`Id`=s2.`AreaId` and s2.`IsDeleted`=0
                              join room_box s3 on s2.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                              left join case_physical_evidence_transfere s4 on s3.`Id`=s4.`LocationId` and s4.`HistoryStatus`=0 and s4.`IsDeleted`=0 and s4.`EvidenceStatus`=1
                              left join case_physical_evidence_transfere s5 on s3.`Id`=s5.`LocationId` and s5.`HistoryStatus`=0 and s5.`IsDeleted`=0 and s5.`EvidenceStatus`=2
                             where s1.`IsDeleted`=0 
                               and s1.`OrganId`=" + organId;
            if(!string.IsNullOrEmpty(areaCode))
                sql += $" AND s1.`Code`='{areaCode}' ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<ThreedimensionalCountEntity>(sql).ToList();
        }
        /// <summary>
        /// 获取设备所属库位详细信息接口
        /// </summary>
        public List<dynamic> QueryCabinetByDeviceCode(long organId, string deviceCode)
        {
            string sql = $@"SELECT s3.`id`,s3.`Total` as cellCode,s3.`name` as cellName 
                              FROM room_cabinet s1
                              join room_box s3 on s1.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                             where s1.`OrganId`={organId} 
                               and s1.`No`='{deviceCode}' ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<dynamic>(sql).ToList();
        }
        /// <summary>
        /// 获取设备物品数量接口
        /// </summary>
        public List<ThreedimensionalCountEntity> QueryCountByDeviceCode(long organId, string deviceCode)
        {
            string sql = $@"SELECT IFNULL(sum(if(s4.`Id`>0, 1, 0)),0) as inCount,
                                   IFNULL(sum(if(s5.`Id`>0, 1, 0)),0) as outCount
                              FROM room_cabinet s2 
                              join room_box s3 on s2.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                              left join case_physical_evidence_transfere s4 on s3.`Id`=s4.`LocationId` and s4.`HistoryStatus`=0 and s4.`IsDeleted`=0 and s4.`EvidenceStatus`=1
                              left join case_physical_evidence_transfere s5 on s3.`Id`=s5.`LocationId` and s5.`HistoryStatus`=0 and s5.`IsDeleted`=0 and s5.`EvidenceStatus`=2
                             where s2.`IsDeleted`=0 
                               and s2.`OrganId`={organId} 
                               and s2.`No`='{deviceCode}' ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<ThreedimensionalCountEntity>(sql).ToList();
        }
        /// <summary>
        /// 获取设备所属位置物证数量接口
        /// </summary>
        public List<ThreedimensionalCountEntity> QueryCountByDeviceCellCode(long organId, string deviceCode, string cellName, int cellCode)
        {
            string sql = $@"SELECT IFNULL(sum(if(s4.`Id`>0, 1, 0)),0) as inCount,
                                   IFNULL(sum(if(s5.`Id`>0, 1, 0)),0) as outCount
                              FROM room_cabinet s2 
                              join room_box s3 on s2.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                              left join case_physical_evidence_transfere s4 on s3.`Id`=s4.`LocationId` and s4.`HistoryStatus`=0 and s4.`IsDeleted`=0 and s4.`EvidenceStatus`=1
                              left join case_physical_evidence_transfere s5 on s3.`Id`=s5.`LocationId` and s5.`HistoryStatus`=0 and s5.`IsDeleted`=0 and s5.`EvidenceStatus`=2
                             where s3.`IsDeleted`=0 
                               and s2.`OrganId`={organId} 
                               and s2.`No`='{deviceCode}' 
                               and s3.`Name` in('{string.Join("','", cellName.Split(','))}') ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<ThreedimensionalCountEntity>(sql).ToList();
        }
        /// <summary>
        /// 获取设备所属位置物品列表接口
        /// </summary>
        public List<dynamic> QueryListByDeviceCellCode(long organId, string deviceCode, string cellName, int cellCode)
        {
            string sql = $@"SELECT s5.`id`,s6.`CaseNo` as 'caseCode',s6.`caseName`,s5.`No` as `propertyCode`,s5.`Name` as `propertyName`,s5.`quantity`,
                                   s7.`Code` as `corpCode`,s7.`Name` as `corpNmae`,s6.`HostUserName` as `owner`,s8.`Code` as warehouseCode,s8.`Name` as warehouseName,
                                   s2.`No` as deviceCode,s2.`Name` as deviceName,0 as cellCode,s3.`Name` as cellName
                              FROM room_cabinet s2 
                              join room_box s3 on s2.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                              join case_physical_evidence_transfere s4 on s3.`Id`=s4.`LocationId` and s4.`HistoryStatus`=0 and s4.`IsDeleted`=0 and s4.`EvidenceStatus`=1 
                              join case_physical_evidence s5 on s4.`EvidenceId`=s5.`Id`
                              join cases s6 on s5.`CaseId`=s6.`Id`
                              join system_organ s7 on s2.`OrganId`=s7.`Id`
                              join room_area s8 on s2.`AreaId`=s8.`Id`
                             where s3.`IsDeleted`=0 
                               and s2.`OrganId`={organId}
                               and s2.`No`='{deviceCode}' 
                               and s3.`Name` in('{string.Join("','", cellName.Split(','))}') ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<dynamic>(sql).ToList();
        }
        /// <summary>
        /// 获取设备所属位置物品详细明细接口
        /// </summary>
        public List<dynamic> QueryPropertyByCode(string propertyCode)
        {
            string sql = $@"SELECT s5.`id`,s6.`CaseNo` as 'caseCode',s6.`caseName`,s5.`No` as `code`,s5.`Name` as `name`,s5.`quantity`,s5.`ClassName` as typeName,
                                   s5.`ExtractMethod` as sourceName,s5.`Status` as registerState,s5.`ExtractionDateTime` as registerTime,s6.`HostUserName` as `owner`,
                                   s7.`Code` as `corpCode`,s7.`Name` as `corpNmae`,s9.`PoliceNo` as organiserCode,s6.`HostUserName` as organiserName,s5.`Unit`,
                                   (case when s5.`Status`=0 then '已登记' when s5.`Status`=1 then '已入库' when s5.`Status`=0 then '已出库' end) as statusName,s5.extractionPersonName as colour,
                                   s5.xingHao as specificationModel,s8.`Code` as warehouseCode,s8.`Name` as warehouseName,
                                   s2.`No` as deviceCode,s2.`Name` as deviceName,0 as cellCode,s3.`Name` as cellName
                              FROM room_cabinet s2 
                              join room_box s3 on s2.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                              join case_physical_evidence_transfere s4 on s3.`Id`=s4.`LocationId` and s4.`HistoryStatus`=0 and s4.`IsDeleted`=0 and s4.`EvidenceStatus`=1 
                              join case_physical_evidence s5 on s4.`EvidenceId`=s5.`Id`
                              join cases s6 on s5.`CaseId`=s6.`Id`
                              join system_organ s7 on s2.`OrganId`=s7.`Id`
                              join room_area s8 on s2.`AreaId`=s8.`Id`
                              left join system_user s9 on s6.`HostUserId`=s9.`Id`
                             where s3.`IsDeleted`=0 
                               and s5.`No`='{propertyCode}' ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<dynamic>(sql).ToList();
        }
        /// <summary>
        /// 获取设备所属位置物品列表接口
        /// </summary>
        public List<dynamic> QueryPropertyList(string warehouseCode, string corpCode, string deviceCode, string caseCode, string caseName, string propertyCode, string propertyName, string owner)
        {
            string sql = $@"SELECT s5.`id`,s6.`CaseNo` as 'caseCode',s6.`caseName`,s5.`No` as `code`,s5.`Name` as `name`,s5.`quantity`,s5.`ClassName` as typeName,
                                   s5.`ExtractMethod` as sourceName,s5.`Status` as registerState,s5.`ExtractionDateTime` as registerTime,s6.`HostUserName` as `owner`,
                                   s7.`Code` as `corpCode`,s7.`Name` as `corpNmae`,s9.`PoliceNo` as organiserCode,s6.`HostUserName` as organiserName,s5.`Unit`,
                                   (case when s5.`Status`=0 then '已登记' when s5.`Status`=1 then '已入库' when s5.`Status`=0 then '已出库' end) as statusName,s5.extractionPersonName as colour,
                                   s5.xingHao as specificationModel,s8.`Code` as warehouseCode,s8.`Name` as warehouseName,
                                   s2.`No` as deviceCode,s2.`Name` as deviceName,0 as cellCode,s3.`Name` as cellName
                              FROM room_cabinet s2 
                              join room_box s3 on s2.`Id`=s3.`CabinetId` and s3.`IsDeleted`=0 
                              join case_physical_evidence_transfere s4 on s3.`Id`=s4.`LocationId` and s4.`HistoryStatus`=0 and s4.`IsDeleted`=0 and s4.`EvidenceStatus`=1 
                              join case_physical_evidence s5 on s4.`EvidenceId`=s5.`Id`
                              join cases s6 on s5.`CaseId`=s6.`Id`
                              join system_organ s7 on s2.`OrganId`=s7.`Id`
                              join room_area s8 on s2.`AreaId`=s8.`Id`
                              left join system_user s9 on s6.`HostUserId`=s9.`Id`
                             where s3.`IsDeleted`=0 
                               and s8.`Code`='{warehouseCode}' ";
            if (!string.IsNullOrEmpty(corpCode))
                sql += $" AND s7.`Code`='{corpCode}' ";
            if (!string.IsNullOrEmpty(deviceCode))
                sql += $" AND s2.`No`='{deviceCode}' ";
            if (!string.IsNullOrEmpty(caseCode))
                sql += $" AND s6.`CaseNo`='{caseCode}' ";
            if (!string.IsNullOrEmpty(caseName))
                sql += $" AND s6.`caseName`='{caseName}' ";
            if (!string.IsNullOrEmpty(propertyCode))
                sql += $" AND s5.`No`='{propertyCode}' ";
            if (!string.IsNullOrEmpty(propertyName))
                sql += $" AND s5.`Name`='{propertyName}' ";
            if (!string.IsNullOrEmpty(owner))
                sql += $" AND s6.`HostUserName`='{owner}' ";
            return this._roomCabinetRepository.GetDb().SqlQueryable<dynamic>(sql).ToList();
        }
        #endregion 3D接口相关数据
    }
}
